package com.jhs.kjs.handler;

import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.web.servlet.view.document.AbstractExcelView;

import com.jhs.kjs.dto.CarDetailInfo_CarDTO;

public class TotalCarListExcelViewHandler extends AbstractExcelView {
	@Override
	protected void buildExcelDocument(Map<String, Object> model, HSSFWorkbook wb,HttpServletRequest request, HttpServletResponse response) throws Exception {
		HSSFSheet sheet = createFirstSheet(wb);
		createColumnLabel(wb, sheet);
		
		@SuppressWarnings("unchecked")
		List<CarDetailInfo_CarDTO> carList = (List<CarDetailInfo_CarDTO>)model.get("carList");
		for(int i=0; i < carList.size(); i++) {
			createPageRow(wb, sheet, carList.get(i), i);
		}
		String fileInfo = String.format("attachment; filename=\"" + createFileName() + "\"");
		response.setHeader("Content-Disposition", fileInfo);
	}
	private String createFileName() {
		return new StringBuilder("totalCarList").append(".xls").toString();
	}
	private HSSFSheet createFirstSheet(HSSFWorkbook workbook) {
		HSSFSheet sheet = workbook.createSheet();
		workbook.setSheetName(0, "전체차량리스트");
		sheet.setColumnWidth(0,2000);
		sheet.setColumnWidth(1,3000);
		sheet.setColumnWidth(2,2700);
		sheet.setColumnWidth(3,3000);
		sheet.setColumnWidth(4,3000);
		sheet.setColumnWidth(5,3300);
		sheet.setColumnWidth(6,4000);
		sheet.setColumnWidth(7,6000);
		sheet.setColumnWidth(8,3000);
		sheet.setColumnWidth(9,3000);
		sheet.setColumnWidth(10,8000);
		sheet.setColumnWidth(11,3000);
		sheet.setColumnWidth(12,2500);
		sheet.setColumnWidth(13,2500);
		sheet.setColumnWidth(14,2500);
		sheet.setColumnWidth(15,2500);
		sheet.setColumnWidth(16,2500);
		sheet.setColumnWidth(17,3000);
		sheet.setColumnWidth(18,2500);
		sheet.setColumnWidth(19,5000);
		sheet.setColumnWidth(20,5000);
		sheet.setColumnWidth(21,4000);
		return sheet;
	}
	
	@SuppressWarnings("deprecation")
	private void createColumnLabel(HSSFWorkbook wb, HSSFSheet sheet) {
		HSSFRow firstRow = sheet.createRow(0);
		HSSFCellStyle titleStyle = wb.createCellStyle();
		
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);		

		// 타이틀 설정
		String[] titleList = { "순번", "차량번호", "운전자명", "연락처", "소속", "차종", "운반물내용", "보고시간", "시/도", "시/구/군", "동/면", "차량상태", "차량속도", "DTG", "CDMA", "GPS", "전압(V)", "방향", "관심지역", "거리(Km)", "누적거리(Km)", "단말기번호"};
		
		for (int i = 0; i < titleList.length; i++) {
			HSSFCell cell = firstRow.createCell(i);
			cell.setCellValue(new HSSFRichTextString(titleList[i]));
			cell.setCellStyle(titleStyle);
		}
		/*HSSFCell cell = firstRow.createCell(0);
		cell.setCellValue("순번");
		
		cell = firstRow.createCell(1);
		cell.setCellValue("차량번호");
		
		cell = firstRow.createCell(2);
		cell.setCellValue("운전자명");
		
		cell = firstRow.createCell(3);
		cell.setCellValue("연락처");
		
		cell = firstRow.createCell(4);
		cell.setCellValue("소속");
		
		cell = firstRow.createCell(5);
		cell.setCellValue("차종");
		
		cell = firstRow.createCell(6);
		cell.setCellValue("운반물내용");
		
		cell = firstRow.createCell(7);
		cell.setCellValue("보고시간");
		
		cell = firstRow.createCell(8);
		cell.setCellValue("시/도");
		
		cell = firstRow.createCell(9);
		cell.setCellValue("시/구/군");
		
		cell = firstRow.createCell(10);
		cell.setCellValue("동/면");
		
		cell = firstRow.createCell(11);
		cell.setCellValue("차량상태");
		
		cell = firstRow.createCell(12);
		cell.setCellValue("차량속도");
		
		cell = firstRow.createCell(13);
		cell.setCellValue("DTG");
		
		cell = firstRow.createCell(14);
		cell.setCellValue("CDMA");
		
		cell = firstRow.createCell(15);
		cell.setCellValue("GPS");
		
		cell = firstRow.createCell(16);
		cell.setCellValue("전압(V)");
		
		cell = firstRow.createCell(17);
		cell.setCellValue("방향");
		
		cell = firstRow.createCell(18);
		cell.setCellValue("관심지역");
		
		cell = firstRow.createCell(19);
		cell.setCellValue("거리(Km)");
		
		cell = firstRow.createCell(20);
		cell.setCellValue("누적거리(Km)");
		
		cell = firstRow.createCell(21);
		cell.setCellValue("단말기번호");*/
		
	}

	@SuppressWarnings("deprecation")
	private void createPageRow(HSSFWorkbook wb, HSSFSheet sheet, CarDetailInfo_CarDTO carListInfo, int rowNum) {
		HSSFRow row = sheet.createRow(rowNum +1);
		
		HSSFCell cell = row.createCell(0);
		cell.setCellValue(carListInfo.getRownum());
		
		cell = row.createCell(1);
		cell.setCellValue(carListInfo.getCarRegiNumber());
		
		cell = row.createCell(2);
		cell.setCellValue(carListInfo.getDri_Name());
		
		cell = row.createCell(3);
		cell.setCellValue(carListInfo.getDri_HandPhone());
		
		cell = row.createCell(4);
		cell.setCellValue(carListInfo.getDep_DeptName());
		
		cell = row.createCell(5);
		cell.setCellValue(carListInfo.getTyp_CarTypeName());
		
		cell = row.createCell(6);
		cell.setCellValue(carListInfo.getBag_BaggageName());
		
		cell = row.createCell(7);
		
		HSSFCellStyle dateStyle = wb.createCellStyle();
		dateStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);	
		dateStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy-MM-dd a hh:mm:ss"));
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd a hh:mm:ss");
		if (carListInfo.getDetectTime() != null) {
			cell.setCellValue(new HSSFRichTextString(dateFormat.format(carListInfo.getDetectTime())));
		} else {
			cell.setCellValue(new HSSFRichTextString(""));
		}
		cell.setCellStyle(dateStyle);
		cell = row.createCell(8);
		cell.setCellValue(carListInfo.getAddressComponent1());
		
		cell = row.createCell(9);
		cell.setCellValue(carListInfo.getAddressComponent2());
		
		cell = row.createCell(10);
		cell.setCellValue(carListInfo.getAddressComponent3());
		
		cell = row.createCell(11);
		cell.setCellValue("시동"+carListInfo.getStartUp());
		
		cell = row.createCell(12);
		cell.setCellValue(carListInfo.getSpeed()==null?0:carListInfo.getSpeed());
		
		cell = row.createCell(13);
		cell.setCellValue(carListInfo.getDev_DTGState());
		
		cell = row.createCell(14);
		cell.setCellValue(carListInfo.getDev_CDMAState());
		
		cell = row.createCell(15);
		cell.setCellValue(carListInfo.getDev_GPSState());
		
		cell = row.createCell(16);
		cell.setCellValue(carListInfo.getVoltage() +((carListInfo.getVoltage() != null && carListInfo.getVoltage() > 0)? "V":""));
		
		cell = row.createCell(17);
		cell.setCellValue(carListInfo.getCadinalStr());
		
		cell = row.createCell(18);
		cell.setCellValue(carListInfo.getCheckArea());
		
		cell = row.createCell(19);
		cell.setCellValue(carListInfo.getDayDistanceStr());
		
		cell = row.createCell(20);
		cell.setCellValue(carListInfo.getTotalDistanceStr());
		
		cell = row.createCell(21);
		cell.setCellValue(carListInfo.getDev_DeviceID());
	}
		/*HSSFSheet sheet = wb.createSheet();
		int rowIdx = 0;

		HSSFCellStyle titleStyle = wb.createCellStyle();
		titleStyle.setFillForegroundColor(HSSFColor.TURQUOISE.index);
		titleStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
		titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);

		// 타이틀 설정
		String[] titleList = { "ID", "NAME", "DATE"};
		HSSFRow titleRow = sheet.createRow(rowIdx++);
		for (int i = 0; i < titleList.length; i++) {
			HSSFCell cell = titleRow.createCell(i);
			cell.setCellValue(new HSSFRichTextString(titleList[i]));
			cell.setCellStyle(titleStyle);
		}

		// 엑셀 서식 설정
		HSSFCellStyle numStyle = wb.createCellStyle();
		numStyle.setDataFormat(wb.createDataFormat().getFormat("#,##0"));
		HSSFCellStyle percentStyle = wb.createCellStyle();
		percentStyle.setDataFormat(wb.createDataFormat().getFormat("0%"));
		HSSFCellStyle dateStyle = wb.createCellStyle();
		dateStyle.setDataFormat(wb.createDataFormat().getFormat("yyyy/mm/dd"));
		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");

		// 데이터 추가
		

		for (User user : list) {
			HSSFRow dataRow = sheet.createRow(rowIdx++);
			HSSFCell idxCell = dataRow.createCell(0);
			idxCell.setCellValue(new HSSFRichTextString(String.format("%d", user.getId())));

			HSSFCell nameCell = dataRow.createCell(1);
			nameCell.setCellValue(new HSSFRichTextString(user.getName()));

			HSSFCell dateCell = dataRow.createCell(2);
			dateCell.setCellValue(new HSSFRichTextString(dateFormat.format(user.getDate())));
		}

		for (int i = 0; i < titleList.length; i++) {
			sheet.autoSizeColumn((short)i);
		}
		// 파일 다운로드 시작
		String fileInfo = String.format("attachment; filename=\"" + createFileName() + "\"");
		response.setHeader("Content-Disposition", fileInfo);*/
	

	/*private String createFileName() {
		SimpleDateFormat fileFormat = new SimpleDateFormat("yyyyMMdd_HHmmss");
		return new StringBuilder("UserList").append("-").append(fileFormat.format(new Date())).append(".xls").toString();

	}*/

}
